﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InstrumentCheck.Module;
using InstrumentCheck.SQLHelper;
using System.Data;
using System.Data.SqlClient;

namespace InstrumentCheck.DAL
{
    public class QueryPR_dal
    {
        public QueryPR_dal()
        { }

        public List<PR> GetQueryInfo_dal(string strQueryTable, string strQueryValue)
        {
            List<PR> list = new List<PR>();
            string sql = "";
            if (strQueryTable.Trim().ToUpper() == "RESDA")
                sql = GenerateQuerySQL();
            if (strQueryTable.Trim().ToUpper() == "CORRECTQUERY")
                sql = CorrectQuerySQL(strQueryValue);

            SqlDataAdapter da = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SqlParameter[] para = new SqlParameter[1];
            para[0] = SQLHelper.SQLHelper.CreateParameter("queryValue", SqlDbType.NVarChar, strQueryValue, ParameterDirection.Input);
            SQLHelper.SQLHelper.Fill(da, ds, sql, CommandType.Text, para);

            if (ds.Tables[0].Rows.Count == 0)
                return null;
            
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                list.Add(GeneratePR(row));
            }
            return list;
            
        }
        private string CorrectQuerySQL(string strQueryValue)
        {
            string sql = "select top 300 a.resda002 PRNO,a.resda015 ApplyTime,a.resda017 ApplicantNO,a.resda021 status,";
            sql += " k.resak002 ApplicantName,k.resak015 ApplicantDept from EForm.EF2KWEB.dbo.resda a,EForm.EF2KWEB.dbo.resak k";
            sql += " where a.resda017 = k.resak001 and a.resda002 like @queryValue and a.resda002 in ";
             sql += " (select PR_NO  from Correct) order by a.resda002 ";
            return sql;
        }

        private string GenerateQuerySQL()
        {
            string sql = "select top 300 a.resda002 PRNO,a.resda015 ApplyTime,a.resda017 ApplicantNO,a.resda021 status,";
            sql += " k.resak002 ApplicantName,k.resak015 ApplicantDept from EForm.EF2KWEB.dbo.resda a,EForm.EF2KWEB.dbo.resak k";
            sql += " where a.resda017 = k.resak001 and a.resda001='EF019' and a.resda021='2' and a.resda002 like @queryValue order by a.resda002 ";
            return sql;
        }
        private PR GeneratePR(DataRow row)
        {
            PR pr = new PR();
            pr.PRNO = row["PRNO"].ToString();
            pr.ApplyDate = Convert.ToDateTime(row["ApplyTime"].ToString());
            pr.PRStatus = (FormStatus)int.Parse(row["status"].ToString());
            User user = new User();
            user.EmpNO = row["ApplicantNO"].ToString();
            user.EmpChineseName = row["ApplicantName"].ToString();
            user.DeptNO = row["ApplicantDept"].ToString();
            pr.PRApplicant = user;
            return pr;
        }
    }
}
